﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;

namespace Project_Udrinkwater
{
    public class ClassSalary
    {
        string Connection = ConfigurationManager.ConnectionStrings["DB_Udrink"].ConnectionString;
        public DataTable CheckDayOfYear(string Year)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" declare @D1 date; ");
            strSQL.Append(" declare @D2 date; ");
            strSQL.Append(" set @D1 = '"+Year+"-01-01'; ");
            strSQL.Append(" set @D2 = '"+Year+"-12-31'; ");
            strSQL.Append(" with C(TheDate) as (select @D1 union all select dateadd(day, 1, C.TheDate) from C where C.TheDate < @D2) ");
            strSQL.Append("  select cast(dateadd(YEAR, datediff(YEAR, 0, C.TheDate), 0) as date) as YEAR, count(*) as DayCount from C ");
            strSQL.Append(" group by dateadd(YEAR, datediff(YEAR, 0, C.TheDate), 0) ");
            strSQL.Append(" option (maxrecursion 0) ");
            string sqltext = strSQL.ToString();
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        public DataTable GetData_NumberEmployee()
        {
            string sqltext = @"SELECT Employee_ID FROM TB_Employee WHERE Employee_ID <> 'INTERNET'";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Absence31(string Emp,string Month,string Year)
        {
            string sqltext = @"SELECT * FROM TB_Time_Cards 
                                WHERE Employee_ID = '"+Emp+"' AND [Check_in] BETWEEN '" + Year + "-" + Month + "-01' AND '" + Year + "-" + Month + "-31'";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Absence30(string Emp, string Month, string Year)
        {
            string sqltext = @"SELECT * FROM TB_Time_Cards 
                                WHERE Employee_ID = '" + Emp + "' AND [Check_in] BETWEEN '" + Year + "-" + Month + "-01' AND '" + Year + "-" + Month + "-30'";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Absence29(string Emp, string Month, string Year)
        {
            string sqltext = @"SELECT * FROM TB_Time_Cards 
                                WHERE Employee_ID = '" + Emp + "' AND [Check_in] BETWEEN '" + Year + "-" + Month + "-01' AND '" + Year + "-" + Month + "-29'";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Absence28(string Emp, string Month, string Year)
        {
            string sqltext = @"SELECT * FROM TB_Time_Cards 
                                WHERE Employee_ID = '" + Emp + "' AND [Check_in] BETWEEN '" + Year + "-" + Month + "-01' AND '" + Year + "-" + Month + "-28'";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Overtime31(string Emp, string Month, string Year)
        {
            string sqltext = @"SELECT SUM(Total_OT) AS TOTAL FROM TB_EmployeeOT 
                                WHERE Employee_ID = '" + Emp + "' AND [Check_in] BETWEEN '" + Year + "-" + Month + "-01' AND '" + Year + "-" + Month + "-31'";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Overtime30(string Emp, string Month, string Year)
        {
            string sqltext = @"SELECT SUM(Total_OT) AS TOTAL FROM TB_EmployeeOT 
                                WHERE Employee_ID = '" + Emp + "' AND [Check_in] BETWEEN '" + Year + "-" + Month + "-01' AND '" + Year + "-" + Month + "-30'";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Overtime29(string Emp, string Month, string Year)
        {
            string sqltext = @"SELECT SUM(Total_OT) AS TOTAL FROM TB_EmployeeOT 
                                WHERE Employee_ID = '" + Emp + "' AND [Check_in] BETWEEN '" + Year + "-" + Month + "-01' AND '" + Year + "-" + Month + "-29'";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Overtime28(string Emp, string Month, string Year)
        {
            string sqltext = @"SELECT SUM(Total_OT) AS TOTAL FROM TB_EmployeeOT 
                                WHERE Employee_ID = '" + Emp + "' AND [Check_in] BETWEEN '" + Year + "-" + Month + "-01' AND '" + Year + "-" + Month + "-28'";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Withdraw31(string Emp, string Month, string Year)
        {
            string sqltext = @"SELECT SUM(Num_withdraw) AS TotalEmp FROM TB_withdraw 
                                WHERE Employee_ID = '" + Emp + "' AND Detial='เบิกล่วงหน้า' AND Date_withdraw BETWEEN '" + Year + "-" + Month + "-01' AND '" + Year + "-" + Month + "-31'";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Withdraw30(string Emp, string Month, string Year)
        {
            string sqltext = @"SELECT SUM(Num_withdraw) AS TotalEmp FROM TB_withdraw 
                                WHERE Employee_ID = '" + Emp + "' AND Detial='เบิกล่วงหน้า' AND Date_withdraw BETWEEN '" + Year + "-" + Month + "-01' AND '" + Year + "-" + Month + "-30'";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Withdraw29(string Emp, string Month, string Year)
        {
            string sqltext = @"SELECT SUM(Num_withdraw) AS TotalEmp FROM TB_withdraw 
                                WHERE Employee_ID = '" + Emp + "' AND Detial='เบิกล่วงหน้า' AND Date_withdraw BETWEEN '" + Year + "-" + Month + "-01' AND '" + Year + "-" + Month + "-29'";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Withdraw28(string Emp, string Month, string Year)
        {
            string sqltext = @"SELECT SUM(Num_withdraw) AS TotalEmp FROM TB_withdraw 
                                WHERE Employee_ID = '" + Emp + "' AND Detial='เบิกล่วงหน้า' AND Date_withdraw BETWEEN '" + Year + "-" + Month + "-01' AND '" + Year + "-" + Month + "-28'";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Commission31(string Emp,string Month, string Year,int Num)
        {
            string sqltext = @"SELECT SUM(TB_Order_List.Price_Unit) AS TOTAL FROM TB_Order
                            INNER JOIN TB_Order_List ON TB_Order.Order_ID = TB_Order_List.Order_ID
                            INNER JOIN TB_Product ON TB_Order_List.Product_ID = TB_Product.Product_ID
                            WHERE TB_Order.Employee_ID = '" + Emp + "' AND TB_Product.Product_Unit=" + Num + " AND (TB_Order.Order_Date_Time BETWEEN '" + Year + "-" + Month + "-1' AND '" + Year + "-" + Month + "-31')";

            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Commission30(string Emp, string Month, string Year, int Num)
        {
            string sqltext = @"SELECT SUM(TB_Order_List.Price_Unit) AS TOTAL FROM TB_Order
                            INNER JOIN TB_Order_List ON TB_Order.Order_ID = TB_Order_List.Order_ID
                            INNER JOIN TB_Product ON TB_Order_List.Product_ID = TB_Product.Product_ID
                            WHERE TB_Order.Employee_ID = '" + Emp + "' AND TB_Product.Product_Unit=" + Num + " AND (TB_Order.Order_Date_Time BETWEEN '" + Year + "-" + Month + "-1' AND '" + Year + "-" + Month + "-30')";

            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Commission28(string Emp, string Month, string Year, int Num)
        {
            string sqltext = @"SELECT SUM(TB_Order_List.Price_Unit) AS TOTAL FROM TB_Order
                            INNER JOIN TB_Order_List ON TB_Order.Order_ID = TB_Order_List.Order_ID
                            INNER JOIN TB_Product ON TB_Order_List.Product_ID = TB_Product.Product_ID
                            WHERE TB_Order.Employee_ID = '" + Emp + "' AND TB_Product.Product_Unit=" + Num + " AND (TB_Order.Order_Date_Time BETWEEN '" + Year + "-" + Month + "-1' AND '" + Year + "-" + Month + "-28')";

            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Commission29(string Emp, string Month, string Year, int Num)
        {
            string sqltext = @"SELECT SUM(TB_Order_List.Price_Unit) AS TOTAL FROM TB_Order
                            INNER JOIN TB_Order_List ON TB_Order.Order_ID = TB_Order_List.Order_ID
                            INNER JOIN TB_Product ON TB_Order_List.Product_ID = TB_Product.Product_ID
                            WHERE TB_Order.Employee_ID = '" + Emp + "' AND TB_Product.Product_Unit=" + Num + " AND (TB_Order.Order_Date_Time BETWEEN '" + Year + "-" + Month + "-1' AND '" + Year + "-" + Month + "-29')";

            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_ShowEmployee(string Month, string Year)
        {
            string sqltext = @"SELECT TB_Employee.Employee_ID, TB_Employee.FirstName, TB_Employee.LastName, TB_Employee.Salary, 
                               TB_Department.Department_Name, TB_Salary.Reduce_Absence, TB_Salary.OT, TB_Salary.Reduce_Withdraw, 
                               TB_Salary.Reduce_Other, TB_Salary.Commission, TB_Salary.Salary_ID,
                               (TB_Employee.Salary-(TB_Salary.Reduce_Withdraw+TB_Salary.OT+TB_Salary.Reduce_Absence+TB_Salary.Reduce_Other+TB_Salary.Commission))AS TOTAL 
                               FROM TB_Employee 
                               INNER JOIN TB_Department ON TB_Employee.Departmernt_ID = TB_Department.Department_ID 
                               INNER JOIN TB_Salary ON TB_Employee.Employee_ID = TB_Salary.Employee_ID
                               WHERE TB_Salary.Salary_StartMonth = '"+Year+"-"+Month+"-01' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
    }
}